package  
{
	import flash.data.SQLConnection;
	import flash.data.SQLStatement;
	import flash.errors.SQLError;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	import org.osflash.signals.Signal;
	/**
	 * ...
	 * @author 
	 */
	public class Statement 
	{
		
		public var text:String;
		public var tblName:String;
		private var _conn:SQLConnection;
		
		public var empty:Boolean;
		public var converted:Boolean;
		public var sql:SQLStatement;
		public var sqlDone:Signal;
		public var sqlError:Signal;
		
		public static var EXCLUDE_TABLES:Array;
		
		
		public function Statement(text:String) 
		{
			sqlDone = new Signal();
			sqlError = new Signal(String);
						sql = new SQLStatement();
			this.text = text;
		}
		
		/**
		 * TODO it should handle multiple INSERT INTO statements. Convert code already in __convert.as
		 */
		public function convert():void
		{
			
			
			
			if (trim()) return;
			
			
			
			//rmv comments
			//src = src.replace(/--.*/gi, ""); //@FIXME
			text = text.replace(/\/\*.+?\*\/$/gis, "");
			
		
			//weird MySQL quotes
			text = text.replace(/`/gi, "\"");
			
			//rmv MySQL COMMENTs ... should also match   COMMENT 'blah')
			text = text.replace(/COMMENT .+?(\"|\')\s*?,/gi, ",");			
			
			//removes line breaks between "),("
			//it helps for replacing INSERT INTOs later
			text = text.replace(/\),\s\(/gis, "),(");
			
			//
			text = text.replace(/SET SQL_MODE=.+/gi, "");
	
			
			//RAND -> RANDOM
			text = text.replace(/RAND\((\d+)\)/gi, "RANDOM($1)");
			
			
		
			
			if (text.substr(0, 11) == "INSERT INTO")
			{
				tblName = text.replace(/INSERT INTO\s*['|"](\w+)['|"].*/is, "$1");
				
				if (EXCLUDE_TABLES.indexOf(tblName) > -1)
				{
					text = "";
				}
			}
			else if (text.substr(0, 12) == "CREATE TABLE")
			{
				
				tblName = text.replace(/CREATE TABLE\s*['|"](\w+)['|"].*/is, "$1");
				
				if (EXCLUDE_TABLES.indexOf(tblName) > -1)
				{
					text = "";
				}
				else
				{
					//rmv table type
					text = text.replace(/(ENGINE|TYPE)=(MyISAM|InnoDB|IBMDB21|MERGE|MEMORY|EXAMPLE|FEDERATED|ARCHIVE|CSV|BLACKHOLE)/gi, "");
					
					//int(n) --> INTEGER
					text = text.replace(/(tiny|small|medium|big)?int\(\d+\)/gi, "INTEGER");
					
					//timestamp -> DATETIME
					//for now timestamp -> varchar, @todo
					//http://www.arckid.com/blog/flash/support-for-timestamp-in-sqlite-with-air-as-local-database/
					text = text.replace(/default\s+?CURRENT_TIMESTAMP/gi, "");
					text = text.replace(/timestamp/gis, "varchar(255)");
					
					//rmv auto_increment
					text = text.replace(/auto_increment(=\d+)?/gi, "");
					
					//rmv UNIQUE_KEY...
					text = text.replace(/,(\s+)?(UNIQUE|PRIMARY)?(\s+)?KEY.+?\(.+?\)/gis, "");
					
					//and replace with PRIMARY KEY
					//newCreate = newCreate.replace(//gi, "");			
								
					//charset stuff
					text = text.replace(/(DEFAULT\s+)?(CHARSET|CHARACTER SET)\s*=?\s*\w+/gi, "");
					
					//collate
					text = text.replace(/(DEFAULT\s+)?COLLATE\s*=?\s*\w+/gi, "");
				}
			}
		
			
		
			//remove blank lines
			//src = text.replace(/;/gis, ";\n");
			text = text.replace(/\r{2,}/gis, "\r\r");
			text = text.replace(/\n{2,}/gis, "\r\r");
			
			
			//remove whtespace at the end of file
			text = text.replace(/;\s*?$/gis, ";");			
			
			
			if (trim()) return;
			
			
		}
		
		
		private function trim():Boolean
		{
			text = StringUtils.trim(text);
			
			if (text == "") empty = true;
			
			if (empty) trace("pty")
			
			return empty;
		}
		
		
		public function save(conn:SQLConnection):void
		{
			_conn = conn;
			
			
			sql.sqlConnection = _conn;
			//sql.addEventListener(SQLEvent.RESULT, onSQLResult);
			sql.addEventListener(SQLErrorEvent.ERROR, onSQLError);
			sql.text = this.text;
			//trace("???" + empty + this.text + sql.text);
			//return
			
			try { sql.execute(); } catch (e:SQLError) { onSQLError(new SQLErrorEvent(SQLErrorEvent.ERROR, false, false, e));  }
			
		}
		
		
		
		private function onSQLResult(e:SQLEvent):void 
		{
			//TODO some weird error here
			//sqlDone.dispatch();
			
		}
					
		
		private function onSQLError(err:SQLErrorEvent):void 
		{

			_conn.close();
			//TBD
			var msg:String = "SQL Error: on instruction# ??, text:\n"  + text.replace(/\r|\n/gis, "");
			trace(msg)
			//sqlError.dispatch(msg); //TODO crashes before it gets triggered
		
		}
				
		
		public function toString():String
		{
			return text;
		}
		
		
		
	}

}